﻿Imports System.Reflection
Imports System.Text.RegularExpressions

Public Delegate Function PropertyFilter(ByVal pro As AttributeMappedProperty) As Boolean

'-------------------------------------------------------------------------------------------
' HOW TO USE.
'
'Public Class TestRecord
'    Property PropertiesHandler As DataRowMappedPropertiesHandler
'    Property Row As DataRow
'    <DbFieldName("id", True)> Property RecordId As Integer
'    <DbFieldName("remark", False)> Property Remark As String

'    Public Sub New()
'        Me.PropertiesHandler = New DataRowMappedPropertiesHandler(Me, "system_settings")
'        Me.PropertiesHandler.ReadDbRecord(36)
'        Me.Remark = "test remark update"
'        Me.PropertiesHandler.UpdateDbRecord()
'    End Sub
'End Class
'
'-------------------------------------------------------------------------------------------

''' <summary>
''' Assume property declarations prepend with required DbFieldNameAttribute.
''' 
''' The custom attributes are extracted and processed to automatic property-to-db-fields operations
''' such as refresh property values from datarow or insert/update/delete datarow using property values.
''' </summary>
''' <remarks></remarks>
Public Class DataRowMappedPropertiesHandler

    Private _parentMappedProperties As Collection
    Private _keyProperty As AttributeMappedProperty

    Public Property Parent As Object
    Public Property TableName As String
    Public Property AutomaticAuditHandling As Boolean = False
    Private Property LastAuditDateTime As String = ""
    Private Property LastAuditUsername As String = ""

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="parent">Define object with which whose properties will be manipulated</param>
    ''' <param name="dbTableName">Define corresponding db table to be mapped.</param>
    ''' <remarks></remarks>
    Public Sub New(parent As Object, dbTableName As String)
        Me.Parent = parent
        Me.TableName = dbTableName
    End Sub

    Public Sub New(parent As Object, dbTableName As String, automaticAudit As Boolean)
        Me.Parent = parent
        Me.TableName = dbTableName
        Me.AutomaticAuditHandling = automaticAudit
    End Sub

    ''' <summary>
    ''' The set of properties with DbFieldNameAttribute attribute defined.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Properties() As Collection

        If _parentMappedProperties Is Nothing Then

            Dim c As Collection = New Collection

            ' For each property of current object
            For Each p As PropertyInfo In Me.Parent.GetType.GetProperties()

                ' If this property has a custom attribute with type name "DbFieldName",
                ' it is associated with a db field
                For Each a As DbFieldNameAttribute In (From o As Object
                                                       In p.GetCustomAttributes(False)
                                                       Where o.GetType.ToString.Contains("DbFieldName")
                                                       )
                    c.Add(New AttributeMappedProperty(Me.Parent, p, a.Name, a.IsKey))
                    Exit For
                Next
            Next

            _parentMappedProperties = c

        End If
        Return _parentMappedProperties

    End Function

    ''' <summary>
    ''' The class property corresponding to a key field in the underlying database table.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function KeyProperty() As AttributeMappedProperty
        If _keyProperty Is Nothing Then
            _keyProperty = (From p As AttributeMappedProperty In Me.Properties Where p.IsKey = True).First
        End If
        Return _keyProperty
    End Function

    ''' <summary>
    ''' Returns a string of description about property value changes comparing to underlying datarow field values.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function PropertiesChanges() As String
        Dim result As String = ""

        If Not Me.Parent.row() Is Nothing Then
            For Each amp As AttributeMappedProperty In Me.DirtyProperties
                result = result + "," + amp.PropertyValueMap()
            Next
            result = result.Trim(",").Replace(",", ", ")
        End If

        If String.IsNullOrEmpty(result) Then result = ""

        Return result
    End Function

    ''' <summary>
    ''' Returns a string of description about property value changes comparing to underlying datarow field values.
    ''' </summary>
    ''' <param name="nameAliases">Replace property name with aliase name in returned result e.g. userfield01 to Visit Date, etc</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function PropertiesChanges(nameAliases As Dictionary(Of String, String)) As String

        Dim result As String = Me.PropertiesChanges()
        If result <> "" Then
            For i = 0 To nameAliases.Count - 1
                result = Regex.Replace(result,
                                       nameAliases.Keys(i) + ":",
                                       nameAliases.Values(i) + ":".Replace(" ", ""),
                                       Text.RegularExpressions.RegexOptions.IgnoreCase)
            Next

        End If
        Return result

    End Function

    Public Function PropertiesContent(ByRef filter As PropertyFilter) As String

        Dim result As String = ""

        If Not Me.Parent.row() Is Nothing Then
            For Each amp As AttributeMappedProperty In Me.Properties
                If filter(amp) Then
                    result = result + "," + amp.PropertyValueMap()
                End If
            Next
            result = result.Trim(",").Replace(",", ", ")
        End If

        If String.IsNullOrEmpty(result) Then result = ""

        Return result

    End Function

    ''' <summary>
    ''' Update property values using datarow fields.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub RefreshPropertiesFromRow()
        If Not Me.Parent.row() Is Nothing Then
            For Each amp As AttributeMappedProperty In Me.Properties
                amp.RefreshPropertyValueFromRow()
            Next
        End If

    End Sub

#Region "DB_OPERATIONS"

    ''' <summary>
    ''' Read the record from database, store it in .Row() property and refresh related properties.
    ''' </summary>
    ''' <param name="recordId"></param>
    ''' <remarks></remarks>
    Public Sub ReadDbRecord(recordId As Integer)

        Dim sql As String = "select * from " + Me.TableName + " where " + Me.KeyProperty.FieldName + "=" + recordId.ToString + " limit 1"
        Dim rowCollection As DataRowCollection = MysqlDataBaseConnection.RunSelect(sql).Tables(0).Rows

        If rowCollection.Count > 0 Then
            Me.Parent.row = rowCollection(0)
        End If
        Me.RefreshPropertiesFromRow()

    End Sub

    ''' <summary>
    ''' Update corresponding db record using property values.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub UpdateDbRecord()
        MysqlDataBaseConnection.RunNonQuery(Me.UpdateRecordString)

        If Me.AutomaticAuditHandling Then
            ' Also update related properties 
            For Each amp As AttributeMappedProperty In (From p As AttributeMappedProperty In Me.Properties Where p.FieldName = "modifydt")
                amp.SetValue(CType(Me.LastAuditDateTime, Date))
            Next
            For Each amp As AttributeMappedProperty In (From p As AttributeMappedProperty In Me.Properties Where p.FieldName = "modifyby")
                amp.SetValue(Me.LastAuditUsername)
            Next
        End If

    End Sub

    Public Function UpdateRecordString() As String
        Dim model As RowModel = Me.DirtyRowModel()
        model.KeyField(Me.KeyProperty.FieldName, Me.KeyProperty.GetValue())

        If Me.AutomaticAuditHandling Then
            Me.LastAuditDateTime = Now.ToString("yyyy-MM-dd HH:mm:ss")
            Me.LastAuditUsername = "system"
            model.SetField("modifydt", Me.LastAuditDateTime)
            model.SetField("modifyby", Me.LastAuditUsername)
        Else
            Me.LastAuditDateTime = ""
            Me.LastAuditUsername = ""
        End If
        Return model.KeyRecordUpdateStatement
    End Function

    ''' <summary>
    ''' Insert a new db record using property values.
    ''' </summary>
    ''' <param name="refreshId">Read key id of newly inserted records and set the corresponding key property value.</param>
    ''' <remarks></remarks>
    Public Sub InsertDbRecord(Optional refreshId As Boolean = False)

        MysqlDataBaseConnection.RunNonQuery(Me.InsertRecordString)

        If Me.AutomaticAuditHandling Then
            For Each amp As AttributeMappedProperty In (From p As AttributeMappedProperty In Me.Properties Where p.FieldName = "modifydt")
                amp.SetValue(CType(Me.LastAuditDateTime, Date))
            Next
            For Each amp As AttributeMappedProperty In (From p As AttributeMappedProperty In Me.Properties Where p.FieldName = "modifyby")
                amp.SetValue(Me.LastAuditUsername)
            Next
        End If

        If refreshId = True Then
            Dim model As RowModel = Me.DirtyRowModel()
            model.SelectField(Me.KeyProperty.FieldName)
            For Each field As FieldModel In Me.DirtyRowModel.SetCollection
                If Not "modifydt,modifyby".Contains(field.Name) Then
                    ' dbfieldmodel object automatically use "IN()" format if value contains ",". 
                    ' To solve the problem, change "," to "*" and the object will change to use " like "abc%def" format
                    model.AndField(field.Name, field.FieldValue.Replace(",", "*"))
                End If
            Next
            Dim sql As String = model.RecordSetSelectStatement + " order by " + Me.KeyProperty.FieldName + " desc limit 1"
            Dim row As DataRow = MysqlDataBaseConnection.RunSelect(sql).Tables(0).Rows(0)

            Me.KeyProperty.SetValue(row(Me.KeyProperty.FieldName))

            ' since already read row from db, update .Row() property. No need to run me.RefreshPropertiesFromRow() to save time
            Me.Parent.row = row

        End If

    End Sub

    Public Function InsertRecordString() As String
        Dim model As RowModel = Me.DirtyRowModel()

        If Me.AutomaticAuditHandling Then
            Me.LastAuditDateTime = Now.ToString("yyyy-MM-dd HH:mm:ss")
            Me.LastAuditUsername = "system"
            model.SetField("modifydt", Me.LastAuditDateTime)
            model.SetField("modifyby", Me.LastAuditUsername)
        End If

        Return model.InsertStatement
    End Function

    ''' <summary>
    ''' Delete db record using the key property value.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub DeleteDbRecord()
        MysqlDataBaseConnection.RunNonQuery(Me.DeleteRecordString)
    End Sub

    Public Function DeleteRecordString()
        Dim model As RowModel = Me.DirtyRowModel()
        model.KeyField(Me.KeyProperty.FieldName, Me.KeyProperty.GetValue())
        Return model.KeyRecordDeleteStatement
    End Function

#End Region

#Region "PRIVATE_SECTION"

    ''' <summary>
    ''' Get DbRowModel object with SetField values for property that are changed comparing to datarow field values.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function DirtyRowModel() As RowModel

        Dim model As RowModel = New RowModel(Me.TableName)
        Dim dateValue As Date

        For Each amp As AttributeMappedProperty In Me.DirtyProperties

            ' Convert all date value to string for update/insert into db
            If amp.GetValue.GetType.ToString.Contains("Date") Then
                dateValue = amp.GetValue
                model.SetField(amp.FieldName, dateValue.ToString("yyyy-MM-dd HH:mm:ss"))
            Else
                model.SetField(amp.FieldName, amp.GetValue())
            End If

        Next
        Return model

    End Function

    ''' <summary>
    ''' Get the set of property objects that are changed comparing to datarow field values.
    ''' Key property object is always not return.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function DirtyProperties() As Collection

        Dim c As Collection = New Collection

        For Each p As AttributeMappedProperty In (From amp As AttributeMappedProperty
                                                   In Me.Properties
                                                   Where Not amp.IsKey And amp.IsDirty)
            c.Add(p)
        Next
        Return c

    End Function

#End Region

End Class

